# +----------------------------------------------------------------------
# | DjangoAdmin敏捷开发框架 [ 赋能开发者，助力企业发展 ]
# +----------------------------------------------------------------------
# | 版权所有 2021~2024 北京DjangoAdmin研发中心
# +----------------------------------------------------------------------
# | Licensed LGPL-3.0 DjangoAdmin并不是自由软件，未经许可禁止去掉相关版权
# +----------------------------------------------------------------------
# | 官方网站: https://www.djangoadmin.cn
# +----------------------------------------------------------------------
# | 作者: @一米阳光 团队荣誉出品
# +----------------------------------------------------------------------
# | 版权和免责声明:
# | 本团队对该软件框架产品拥有知识产权（包括但不限于商标权、专利权、著作权、商业秘密等）
# | 均受到相关法律法规的保护，任何个人、组织和单位不得在未经本团队书面授权的情况下对所授权
# | 软件框架产品本身申请相关的知识产权，禁止用于任何违法、侵害他人合法权益等恶意的行为，禁
# | 止用于任何违反我国法律法规的一切项目研发，任何个人、组织和单位用于项目研发而产生的任何
# | 意外、疏忽、合约毁坏、诽谤、版权或知识产权侵犯及其造成的损失 (包括但不限于直接、间接、
# | 附带或衍生的损失等)，本团队不承担任何法律责任，本软件框架禁止任何单位和个人、组织用于
# | 任何违法、侵害他人合法利益等恶意的行为，如有发现违规、违法的犯罪行为，本团队将无条件配
# | 合公安机关调查取证同时保留一切以法律手段起诉的权利，本软件框架只能用于公司和个人内部的
# | 法律所允许的合法合规的软件产品研发，详细声明内容请阅读《框架免责声明》附件；
# +----------------------------------------------------------------------

import datetime

from fastapi.logger import logger
from sqlalchemy import and_
from werkzeug.datastructures.structures import MultiDict

from apps.constants.message import PAGE_LIMIT
from apps.models.user import User
from apps.models.data import Data
from apps.models.dept import Dept
from apps.models.company import Company
from apps.forms.data import ApiDataForm
from extends import db
from utils import R, regular
from utils.utils import uid
from config.env import FASTAPI_ROOT_PATH, FASTAPI_IMAGE_URL
from openpyxl import Workbook


# 查询分页数据
async def DataList(request):
    try:
        # 页码
        page = int(request.query_params.get("page", 1))
        # 每页数
        limit = int(request.query_params.get("limit", PAGE_LIMIT))
        # 实例化查询对象
        query = db.query(Data).filter(Data.is_delete == 0)
        # 店铺名称
        shop_name = request.query_params.get('shop_name')
        if shop_name:
            query = query.filter(Data.shop_name.like('%' + shop_name + '%'))
        # 公司名称
        company_id = request.query_params.get('company_id', 0)
        if company_id:
            query = query.filter(Data.company_id == company_id)
        year = request.query_params.get('year')
        month = request.query_params.get('month')
        sun = request.query_params.get('sun')
        if year:
            query = query.filter(Data.year == year)
        if month:
            query = query.filter(Data.month == month)
        if sun:
            query = query.filter(Data.sun == sun)
        dept_id = request.query_params.get('dept_id')
        if dept_id:
            query = query.filter(Data.dept_id == dept_id)
        # 用户名
        user_name = request.query_params.get('user_name')
        if user_name:
            users = db.query(User).filter(User.nickname.like('%' + user_name + '%')).all()
            userIdLists = []
            for user in users:
                userIdLists.append(user.id)
            if len(userIdLists) > 0:
                query = query.filter(Data.uid.in_(userIdLists))
        userID = uid(request)
        if userID != 1:  # 不是超级管理员
            user = db.query(User).filter(User.id == userID).first()
            query = query.filter(Data.company_id == user.company_id)
        # 排序
        query = query.order_by(Data.id.desc())
        # 记录总数
        count = query.count()
        # 分页查询
        list = query.limit(limit).offset((page - 1) * limit).all()
        companyList = {}
        if len(list):
            # 查看公司列表
            company_list = db.query(Company).filter(Company.is_delete == 0).all()
            companyList = {}
            if company_list:
                for company in company_list:
                    companyList[company.id] = company.name
        # 实例化数组对象
        result = []
        # 遍历数据源
        if list:
            for item in list:
                # 查找用户
                userRow = db.query(User).filter(User.id == item.uid).first()
                # 查看部门列表
                deptRow = db.query(Dept).filter(and_(Dept.id == item.dept_id)).first()
                # 对象转字典
                data = item.to_dict()
                # 公司
                data['company_name'] = companyList.get(item.company_id)
                # 部门
                data['dept_name'] = deptRow.name
                data['user_name'] = userRow.nickname
                # 加入列表
                result.append(data)
        # 返回结果
        return R.ok(data=result, count=count)
    except:
        # 抛出异常
        logger.error('运行异常')
        raise
    finally:
        # 关闭连接
        db.close()


# 添加数据
async def DataAdd(request):
    # 获取请求参数
    json_data = await request.json()
    # 表单验证
    form = ApiDataForm(MultiDict(json_data))
    if not form.validate():
        # 获取错误描述
        err_msg = regular.get_err(form)
        # 返回错误信息
        return R.failed(msg=err_msg)
    # 根据手机号查询用户记录
    user = db.query(User).filter(and_(User.mobile == form.mobile.data, User.is_delete == 0)).first()
    # 查询结果判空
    if not user:
        return R.failed("用户不存在")
    del form.mobile
    # 判断店铺数据是否存在
    today = datetime.date.today()
    yesterday = today - datetime.timedelta(days=1)
    row = db.query(Data).filter(
        and_(Data.day == yesterday, Data.shop_num == form.mall_id.data)).first()
    if not row:  # 不存在新增
        # 表单数据赋值给对象
        addData = form.data
        addData['uid'] = user.id
        addData['company_id'] = user.company_id
        addData['day'] = yesterday
        addData['dept_id'] = user.dept_id
        addData['year'] = yesterday.strftime('%Y')
        addData['month'] = yesterday.strftime('%m')
        addData['sun'] = yesterday.strftime('%d')
        data = Data(**addData)
        # 插入数据
        data.save()
        # 返回结果
        return R.ok(msg="添加成功")
    else:
        return R.ok(msg="数据已存在")
        # updData = {
        #     'update_time': datetime.datetime.now(),
        #     'order_num': form.order_num.data,
        #     'shop_name': form.shop_name.data,
        # }
        # result = db.query(Data).filter_by(id=row.id).update(updData)
        # # 提交数据
        # db.commit()
        # if not result:
        #     return R.failed("更新失败")
        # # 返回结果
        # return R.ok(msg="更新成功")


# 查询分页数据
async def Export(request):
    try:
        # 页码
        page = int(request.query_params.get("page", 1))
        limit = 50000
        # 实例化查询对象
        query = db.query(Data).filter(Data.is_delete == 0)
        # 店铺名称
        shop_name = request.query_params.get('shop_name')
        if shop_name:
            query = query.filter(Data.shop_name.like('%' + shop_name + '%'))
        userID = uid(request)
        user = db.query(User).filter(User.id == userID).first()
        if userID != 1:  # 不是超级管理员
            query = query.filter(Data.company_id == user.company_id)
        # 查看部门列表
        deptRow = db.query(Dept).filter(and_(Dept.id == user.dept_id)).first()
        # 排序
        query = query.order_by(Data.id.asc())
        # 记录总数
        count = query.count()
        # 分页查询
        list = query.limit(limit).offset((page - 1) * limit).all()
        companyList = {}
        if len(list):
            # 查看公司列表
            company_list = db.query(Company).filter(Company.is_delete == 0).all()
            companyList = {}
            if company_list:
                for company in company_list:
                    companyList[company.id] = company.name
        # 创建一个新的工作簿
        workbook = Workbook()
        # 选择默认的工作表
        active_sheet = workbook.active
        # 向工作表中添加数据
        active_sheet.title = "Sheet1"  # 修改工作表的名称
        # 遍历数据源
        if list:
            for key, item in enumerate(list):
                sheet_num = key + 1
                if sheet_num == 1:
                    active_sheet["A{0}".format(sheet_num)] = "ID"
                    active_sheet["B{0}".format(sheet_num)] = "公司名称"
                    active_sheet["C{0}".format(sheet_num)] = "店铺编号"
                    active_sheet["D{0}".format(sheet_num)] = "店铺名称"
                    active_sheet["E{0}".format(sheet_num)] = "评分"
                    active_sheet["F{0}".format(sheet_num)] = "店铺等级"
                    active_sheet["G{0}".format(sheet_num)] = "成交金额"
                    active_sheet["H{0}".format(sheet_num)] = "成交买家数"
                    active_sheet["I{0}".format(sheet_num)] = "推广金"
                    active_sheet["J{0}".format(sheet_num)] = "客单价"
                    active_sheet["K{0}".format(sheet_num)] = "成交订单数"
                    active_sheet["L{0}".format(sheet_num)] = "退款金额"
                    active_sheet["M{0}".format(sheet_num)] = "退款订单数"
                    active_sheet["N{0}".format(sheet_num)] = "商品成交数"
                    active_sheet["O{0}".format(sheet_num)] = "日期"
                    active_sheet["P{0}".format(sheet_num)] = "部门"
                else:
                    active_sheet["A{0}".format(sheet_num)] = item.id
                    active_sheet["B{0}".format(sheet_num)] = companyList.get(item.company_id)
                    active_sheet["C{0}".format(sheet_num)] = item.shop_num
                    active_sheet["D{0}".format(sheet_num)] = item.shop_name
                    active_sheet["E{0}".format(sheet_num)] = item.score
                    active_sheet["F{0}".format(sheet_num)] = item.level
                    active_sheet["G{0}".format(sheet_num)] = item.sale_money
                    active_sheet["H{0}".format(sheet_num)] = item.sale_num
                    active_sheet["I{0}".format(sheet_num)] = item.tg_amount
                    active_sheet["J{0}".format(sheet_num)] = item.avg_money
                    active_sheet["K{0}".format(sheet_num)] = item.completed_order
                    active_sheet["L{0}".format(sheet_num)] = item.refund_money
                    active_sheet["M{0}".format(sheet_num)] = item.refund_order
                    active_sheet["N{0}".format(sheet_num)] = item.good_num
                    active_sheet["O{0}".format(sheet_num)] = item.day
                    active_sheet["P{0}".format(sheet_num)] = deptRow.name
        # 返回结果
        # 保存工作簿到文件
        file_name = "{0}_output_data.xlsx".format(userID)
        save_path = "/uploads/{0}".format(file_name)
        workbook.save(FASTAPI_ROOT_PATH + save_path)
        return R.ok(data=FASTAPI_IMAGE_URL + "/" + file_name)
    except:
        # 抛出异常
        logger.error('运行异常')
        raise
    finally:
        # 关闭连接
        db.close()
